package com.hqyj.bigdata.hiveproject.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hqyj.bigdata.hiveproject.entity.TopTenCatalogInfo;
import com.hqyj.bigdata.hiveproject.entity.TopTenVideoInfo;
import com.hqyj.bigdata.hiveproject.mapper.TopTenCatalogInfoMapper;
import com.hqyj.bigdata.hiveproject.service.TopTenCatalogInfoService;
import com.hqyj.bigdata.hiveproject.util.HiveConnection;
import org.apache.hadoop.hive.metastore.ObjectStore;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.annotation.Resource;
import javax.transaction.Transactional;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Service
public class TopTenCatalogInfoServiceImpl extends ServiceImpl<TopTenCatalogInfoMapper, TopTenCatalogInfo> implements TopTenCatalogInfoService {

    @Resource
    private TopTenCatalogInfoMapper topTenCatalogInfoMapper;

    //alt+insert出现自动实现方法的提示

    @Transactional(rollbackOn = Exception.class)
    @Override
    public boolean initTopTenCatalogInfoList() throws Exception {
        String sql = "select\n" +
                "   category_name,\n" +
                "   count(*) category_count\n" +
                "from\n" +
                "   (select\n" +
                "   videoId,\n" +
                "   category_name\n" +
                "from\n" +
                "   gulivideo_orc\n" +
                "lateral view explode(category) tmp_category as category_name)t1\n" +
                "group by \n" +
                "   category_name\n" +
                "order by\n" +
                "   category_count desc\n" +
                "limit 10";

        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<TopTenCatalogInfo> topTenCatalogInfos = new ArrayList<TopTenCatalogInfo>();

        try {
            pstm = HiveConnection.getConnection().prepareStatement(sql);
            //boolean flag = pstm.execute(sql);

            rs = pstm.executeQuery(); //得到数据集
            while (rs.next()) {
                //System.out.println(rs.getString("videoid") + "," + rs.getLong("views"));
                TopTenCatalogInfo topTenCatalogInfo = TopTenCatalogInfo.builder().categoryName(rs.getString("category_name")).categoryCount(rs.getLong("category_count")).type(10).build();
                topTenCatalogInfos.add(topTenCatalogInfo);
            }

            return super.saveBatch(topTenCatalogInfos);
        } catch (Exception ex) {
            ex.printStackTrace();
            // 事务的回滚
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return false;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }

                if (pstm != null) {
                    pstm.close();
                    pstm = null;
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    @Transactional(rollbackOn = Exception.class)
    @Override
    public boolean initTopTwentyCatalogInfoList() throws Exception {
        String sql = "select\n" +
                "   category_name,\n" +
                "   count(*) category_count\n" +
                "from\n" +
                "   (select\n" +
                "   explode(category) category_name\n" +
                "from\n" +
                "   (select\n" +
                "   category\n" +
                "from\n" +
                "   (select\n" +
                "   related_id\n" +
                "from\n" +
                "   (select\n" +
                "   relatedId,\n" +
                "   views\n" +
                "from\n" +
                "   gulivideo_orc\n" +
                "order by \n" +
                "   views desc\n" +
                "limit 50)t1\n" +
                "lateral view explode(relatedId) tmp_relatedId as related_id\n" +
                "group by \n" +
                "   related_id)t2\n" +
                "join gulivideo_orc orc\n" +
                "on t2.related_id=orc.videoId)t3)t4\n" +
                "group by \n" +
                "   category_name\n" +
                "order by\n" +
                "   category_count desc";

        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<TopTenCatalogInfo> topTenCatalogInfos = new ArrayList<TopTenCatalogInfo>();

        try {
            pstm = HiveConnection.getConnection().prepareStatement(sql);
            //boolean flag = pstm.execute(sql);

            rs = pstm.executeQuery(); //得到数据集
            while (rs.next()) {
                //System.out.println(rs.getString("videoid") + "," + rs.getLong("views"));
                TopTenCatalogInfo topTenCatalogInfo = TopTenCatalogInfo.builder().categoryName(rs.getString("category_name")).categoryCount(rs.getLong("category_count")).type(20).build();
                topTenCatalogInfos.add(topTenCatalogInfo);
            }

            return super.saveBatch(topTenCatalogInfos);
        } catch (Exception ex) {
            ex.printStackTrace();
            // 事务的回滚
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return false;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }

                if (pstm != null) {
                    pstm.close();
                    pstm = null;
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    @Transactional(rollbackOn = Exception.class)
    @Override
    public List<TopTenCatalogInfo> queryTopTenCatalogInfoList() throws Exception {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("type",10);
        return super.list(queryWrapper);
    }


    @Transactional(rollbackOn = Exception.class)
    @Override
    public List<TopTenCatalogInfo> queryTopTwentyCatalogInfoList() throws Exception {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("type",20);
        return super.list(queryWrapper);
    }
}
